import json

from pandas import DataFrame

from common.mydb import SQL
from common.pd import get_tb_data, DF_Table
from common.config import Public_Data


def is_in_db(col_value_list: list, col_name="file_channel", table_name="idrs") -> list:
    """
        用于检查数据是否包含在数据表中
        col_value_list = 列的值列表
        col_name : 列名
        table_name : 数据表名
    """
    if col_value_list:
        sql_base_str = f' {col_name} in ('
        condition_str = ""
        if type(col_value_list[0]) is str:
            condition_str = sql_base_str + '"' + '","'.join(col_value_list) + '")'

            print(condition_str)
        elif type(col_value_list[0]) is int:
            condition_str = sql_base_str + str(col_value_list).replace("[", "").replace("]", "") + ')'

        df: DataFrame = get_tb_data(table_name, condition_str)
        if df.empty:
            return []
        else:
            datas = df[col_name].values.tolist()
            return datas


def get_loc_unique_list(doc_df: DataFrame, col_name):
    """获取dataframe 数据中的某列的不重复值"""
    return doc_df[col_name].unique().tolist()


# region fax 相关操作

# endregion

# region doc_f 相关操作

def doc_df_to_review_tb(df_doc: DataFrame, is_idrs=0):
    info_list = doc_df_to_list(df_doc, is_idrs)
    if is_idrs:
        doc_list_to_idrs(info_list)
    else:
        doc_list_to_drcs(info_list)


def doc_df_to_list(df_doc: DataFrame, is_idrs=0):
    """将文件存储至drcs或IDRS表"""
    if not df_doc.empty:
        # 获取文件传递单列表
        channel_code_list = get_loc_unique_list(df_doc, "channel")
        review_channel_info_list = []
        if channel_code_list:
            for channel in channel_code_list:
                df_chanel_doc = df_doc.loc[df_doc["channel"] == channel]  # 获取通道号中的所有文件
                doc_info: DataFrame = df_chanel_doc[["id", "code", "rev", "title", "contract_id"]]
                doc_inf_dic = doc_info.to_dict("list")
                title_list = doc_inf_dic["title"]
                print(doc_inf_dic)
                contract_list = doc_inf_dic["contract_id"]
                doc_code_list: list = doc_inf_dic["code"]
                doc_rev_list: list = doc_inf_dic["rev"]
                doc_code_info = doc_code_list_to_doc_str(doc_code_list, title_list, doc_rev_list)
                title = "制造厂提交文件"
                contract_id = contract_list[0]
                if len(title_list) == 1:
                    title = title_list[0]
                elif len(title_list) > 1:
                    title = title_list[0] + "等文件"

                if is_idrs:

                    comment = "请审查制造厂提交的文件：" + title
                    title = title + " 协审"
                    data = [channel, title, comment, doc_code_info, contract_id]
                else:
                    title = title + "的审查意见"
                    comment = "详见文件清单中的具体审查意见"
                    contract_dic = get_drcs_info_from_contract_id(contract_id)
                    project = contract_dic["project"]
                    checker = contract_dic["checker"]
                    approver = contract_dic["approver"]
                    receiver_code = contract_dic["channel_code"]
                    file_copy = contract_dic["file_copy"]
                    data = [channel, title, comment, doc_code_info, contract_id, project, receiver_code, checker,
                            approver, file_copy]
                review_channel_info_list.append(data)
            return review_channel_info_list


def get_reviewed_name(doc_channel, title_list, is_idrs=0):
    title = "制造厂提交文件"
    if len(title_list) == 1:
        title = title_list[0]
    elif len(title_list) > 1:
        title = title_list[0] + "等文件"

    if is_idrs:
        title = title + " 协审"
    else:
        title = title + "的审查意见"
    return title


# endregion

# region idrs和drcs 表的相关操作
# fax to idrs
def insert_fax_to_idrs(df_fax: DataFrame):
    """
    把fax：DataFrame 添加到IDRS的数据表中
    """
    if not df_fax.empty:
        row_index_list = df_fax.index.tolist()
        datas = []
        for index in row_index_list:
            file_channel = df_fax.loc[index, "channel"]
            fax_title = df_fax.loc[index, "title"].strip()

            title = f"关于函件《{fax_title}》的协审"
            project = df_fax.loc[index, "project"].strip()
            contract_id = int(df_fax.loc[index, "contract_id"])
            comment = f"请审查制造厂提交的函件《{fax_title}》"
            datas.append([project, file_channel, title, contract_id, comment])
        sql = SQL()
        idrs_col_name_list = ['project', "file_channel", 'title', 'contract_id', 'comment']
        sql.insert_many("idrs", idrs_col_name_list, datas)
        return {"rst": True, "data": None, "msg": f"添加{len(datas)}个函件的协审成功，请到IDRS页面发起流程"}
    return {"rst": False, "data": None, "msg": f"未生成要发起的函件IDRS"}


# doc to idrs
def doc_list_to_idrs(doc_list):
    table = DF_Table('idrs')
    col_index = ["file_channel", "title", "comment", "doc_json", "contract_id"]
    if doc_list:
        table.insert_rows(doc_list, col_index)


def doc_code_list_to_doc_str(doc_code_list: list, doc_title_list: list, doc_rev_list: list = None) -> str:
    """将文件列表转为DCRS 文件字符串"""
    if doc_code_list:
        docs_dic = {}
        for index, doc_code in enumerate(doc_code_list):
            doc_rev = ""
            if doc_rev_list:
                doc_rev = doc_rev_list[index]
            doc_dic = {
                "doc_title": doc_title_list[index],
                "doc_rev": doc_rev,
                'doc_review': '无意见',
                'doc_result': 'ACC'
            }
            docs_dic[doc_code.strip()] = doc_dic
        doc_info = json.dumps(docs_dic)
        print(doc_info)
        return doc_info


def doc_list_to_drcs(doc_list):
    drcs_table = DF_Table('drcs')

    col_index = ["file_channel", "title", "comment", "doc_json", "contract_id", "project", "receiver_code", "checker",
                 "approver", "cc"]
    if doc_list:
        drcs_table.insert_rows(doc_list, col_index)


def get_drcs_info_from_contract_id(contract_id: int):
    """通过合同id从合同中查询关于DRCS的信息"""
    contract_info: dict = {}
    df_contract = Public_Data().Df_Contract
    contract_info_df = df_contract.loc[df_contract["id"] == contract_id]
    con_info: DataFrame = contract_info_df[["project", "checker", "approver", "channel_code", 'file_copy']]
    con_inf_dic = con_info.to_dict("list")
    contract_info["project"] = con_inf_dic["project"][0]
    contract_info["checker"] = con_inf_dic["checker"][0]
    contract_info["approver"] = con_inf_dic["approver"][0]
    contract_info["channel_code"] = con_inf_dic["channel_code"][0]
    contract_info["file_copy"] = con_inf_dic["file_copy"][0]
    return contract_info


def delete_idrs(id_list):
    delete_by_id_list("idrs", id_list)


def delete_drcs(id_list):
    delete_by_id_list("drcs", id_list)


def delete_by_id_list(table_name, id_list):
    if id_list:
        sql_str = f'DELETE FROM {table_name} WHERE id = ?'
        sql = SQL()
        sql.execute_many_datas(sql_str, id_list)


def update_status(table_name, id, status_value):
    if id:
        sql_str = f'UPDATE FROM {table_name} SET status={status_value} WHERE id = {id}'
        sql = SQL()
        sql.execute_cmd(sql_str)


def up_date_drcs_status(id, status_value):
    update_status("drcs", id, status_value)


def up_date_idrs_status(id, status_value):
    update_status("idrs", id, status_value)

# endregion
